using ClosedXML.Excel;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using PHMEE.EnergyEfficiency;
using Volo.Abp;

namespace PHMEE.ConsoleTools.EnergyEfficiency;

public class VesselEeConfigImporter
{
    public const string CommandName = "import-vessel-ee-config";
    
    private IVesselEeConfigRepository _vesselEeConfigRepository;
    private ILogger<VesselEeConfigImporter> _logger;

    public async Task RunCommandAsync(string[] commandParts, IServiceProvider serviceProvider)
    {
        if (commandParts.Length != 2)
        {
            throw new UserFriendlyException("请提供 Vessel Ee Config 的 Excel 文件路径");
        }
        
        var filePath = commandParts[1];
        
        if (string.IsNullOrWhiteSpace(filePath))
        {
            throw new UserFriendlyException("Excel 文件路径不能为空");
        }
        
        _vesselEeConfigRepository = serviceProvider.GetRequiredService<IVesselEeConfigRepository>();
        _logger = serviceProvider.GetRequiredService<ILogger<VesselEeConfigImporter>>();
        
        await ImportAsync(filePath);
    }

    private async Task ImportAsync(string filePath)
    {
        await using var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        using var workbook = new XLWorkbook(fileStream);
        
        var configSheet = workbook.Worksheet("Config") ?? throw new UserFriendlyException("Excel 文件中没有 Config Sheet");

        VesselEeConfig? vesselEeConfig;
        
        var row3 = configSheet.Row(3);
        if (row3.Cell("A").GetValue<string>() == "Id")
        {
            var idStr = row3.Cell("B").GetValue<string>();
            if (!Guid.TryParse(idStr, out var id))
            {
                throw new UserFriendlyException("Id 不是有效的 Guid");
            }
            
            // 查询数据库是否已存在该 VesselEeConfig
            vesselEeConfig = await _vesselEeConfigRepository.GetByIdAsync(id);
            if (vesselEeConfig is not null)
            {
                _logger.LogWarning("VesselEeConfig 已存在，无需导入，Id: {Id}", id);
                return;
            }
            
            vesselEeConfig = new VesselEeConfig(id);
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第三行 A 列不是 Id");
        }
        
        var row4 = configSheet.Row(4);
        if (row4.Cell("A").GetValue<string>() == "FleetId")
        {
            var fleetIdStr = row4.Cell("B").GetValue<string>();
            if (!Guid.TryParse(fleetIdStr, out var fleetId))
            {
                throw new UserFriendlyException("FleetId 不是有效的 Guid");
            }
            
            vesselEeConfig.FleetId = fleetId;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第四行 A 列不是 FleetId");
        }
        
        var row5 = configSheet.Row(5);
        if (row5.Cell("A").GetValue<string>() == "Name")
        {
            var name = row5.Cell("B").GetValue<string>();
            vesselEeConfig.Name = name;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第五行 A 列不是 Name");
        }
        
        // IMO
        var row6 = configSheet.Row(6);
        if (row6.Cell("A").GetValue<string>() == "IMO")
        {
            var imo = row6.Cell("B").GetValue<int>();
            vesselEeConfig.IMO = imo;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第六行 A 列不是 IMO");
        }
        
        // CallSign
        var row7 = configSheet.Row(7);
        if (row7.Cell("A").GetValue<string>() == "CallSign")
        {
            var callSign = row7.Cell("B").GetValue<string>();
            vesselEeConfig.CallSign = callSign;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第七行 A 列不是 CallSign");
        }
        
        // VesselType
        var row8 = configSheet.Row(8);
        if (row8.Cell("A").GetValue<string>() == "VesselType")
        {
            var vesselType = row8.Cell("B").GetValue<int>();
            vesselEeConfig.VesselType = (VesselType)vesselType;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第八行 A 列不是 VesselType");
        }
        
        // NT
        var row9 = configSheet.Row(9);
        if (row9.Cell("A").GetValue<string>() == "NT")
        {
            var nt = row9.Cell("B").GetValue<int>();
            vesselEeConfig.NT = nt;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第九行 A 列不是 NT");
        }
        
        // GT
        var row10 = configSheet.Row(10);
        if (row10.Cell("A").GetValue<string>() == "GT")
        {
            var gt = row10.Cell("B").GetValue<int>();
            vesselEeConfig.GT = gt;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第十行 A 列不是 GT");
        }
        
        // DWT
        var row11 = configSheet.Row(11);
        if (row11.Cell("A").GetValue<string>() == "DWT")
        {
            var dwt = row11.Cell("B").GetValue<int>();
            vesselEeConfig.DWT = dwt;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第十一行 A 列不是 DWT");
        }
        
        // FlagState
        var row12 = configSheet.Row(12);
        if (row12.Cell("A").GetValue<string>() == "FlagState")
        {
            var flagState = row12.Cell("B").GetValue<string>();
            vesselEeConfig.FlagState = flagState;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第十二行 A 列不是 FlagState");
        }
        
        // ClassificationSociety
        var row13 = configSheet.Row(13);
        if (row13.Cell("A").GetValue<string>() == "ClassificationSociety")
        {
            var classificationSociety = row13.Cell("B").GetValue<string>();
            vesselEeConfig.ClassificationSociety = classificationSociety;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第十三行 A 列不是 ClassificationSociety");
        }
        
        // FuelTypes
        var row14 = configSheet.Row(14);
        if (row14.Cell("A").GetValue<string>() == "FuelTypes")
        {
            var fuelTypes = row14.Cell("B").GetValue<string>();
            // 根据 , 符号分割字符串，并转换为 FuelType 枚举
            var fuelTypeStrs = fuelTypes.Split(',');
            var fuelTypeList = new List<FuelType>();
            foreach (var fuelTypeStr in fuelTypeStrs)
            {
                if (Enum.TryParse<FuelType>(fuelTypeStr.Trim(), out var fuelType))
                {
                    fuelTypeList.Add(fuelType);
                }
                else
                {
                    throw new UserFriendlyException($"FuelType {fuelTypeStr} 无法转换为有效的 FuelType 枚举");
                }
            }
            
            vesselEeConfig.FuelTypes = fuelTypeList;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第十四行 A 列不是 FuelTypes");
        }
        
        // EEDI
        var row15 = configSheet.Row(15);
        if (row15.Cell("A").GetValue<string>() == "EEDI")
        {
            var eedi = row15.Cell("B").GetValue<float>();
            vesselEeConfig.EEDI = eedi;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第十五行 A 列不是 EEDI");
        }
        
        // EEXI
        var row16 = configSheet.Row(16);
        if (row16.Cell("A").GetValue<string>() == "EEXI")
        {
            var eexi = row16.Cell("B").GetValue<float>();
            vesselEeConfig.EEXI = eexi;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第十六行 A 列不是 EEXI");
        }
        
        // IceClass
        var row17 = configSheet.Row(17);
        if (row17.Cell("A").GetValue<string>() == "IceClass")
        {
            var iceClass = row17.Cell("B").GetValue<int>();
            vesselEeConfig.IceClass = (IceClass)iceClass;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第十七行 A 列不是 IceClass");
        }
        
        // MainPropulsionPowerOutput
        var row18 = configSheet.Row(18);
        if (row18.Cell("A").GetValue<string>() == "MainPropulsionPowerOutput")
        {
            var mainPropulsionPowerOutput = row18.Cell("B").GetValue<string>();
            vesselEeConfig.MainPropulsionPowerOutput = mainPropulsionPowerOutput;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第十八行 A 列不是 MainPropulsionPowerOutput");
        }
        
        // AuxiliaryEnginesPowerOutput
        var row19 = configSheet.Row(19);
        if (row19.Cell("A").GetValue<string>() == "AuxiliaryEnginesPowerOutput")
        {
            var auxiliaryEnginesPowerOutput = row19.Cell("B").GetValue<string>();
            vesselEeConfig.AuxiliaryEnginesPowerOutput = auxiliaryEnginesPowerOutput;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第十九行 A 列不是 AuxiliaryEnginesPowerOutput");
        }
        
        // ManagementCompany
        var row20 = configSheet.Row(20);
        if (row20.Cell("A").GetValue<string>() == "ManagementCompany")
        {
            var managementCompany = row20.Cell("B").GetValue<string>();
            vesselEeConfig.ManagementCompany = managementCompany;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第二十行 A 列不是 ManagementCompany");
        }
        
        // FtTanker
        var row21 = configSheet.Row(21);
        if (row21.Cell("A").GetValue<string>() == "FtTanker")
        {
            var ftTanker = row21.Cell("B").GetValue<float>();
            vesselEeConfig.FtTanker = ftTanker;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第二十一行 A 列不是 FtTanker");
        }
        
        // Fi
        var row22 = configSheet.Row(22);
        if (row22.Cell("A").GetValue<string>() == "Fi")
        {
            var fi = row22.Cell("B").GetValue<float>();
            vesselEeConfig.Fi = fi;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第二十二行 A 列不是 Fi");
        }
        
        // Fm
        var row23 = configSheet.Row(23);
        if (row23.Cell("A").GetValue<string>() == "Fm")
        {
            var fm = row23.Cell("B").GetValue<float>();
            vesselEeConfig.Fm = fm;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第二十三行 A 列不是 Fm");
        }
        
        // Fc
        var row24 = configSheet.Row(24);
        if (row24.Cell("A").GetValue<string>() == "Fc")
        {
            var fc = row24.Cell("B").GetValue<float>();
            vesselEeConfig.Fc = fc;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第二十四行 A 列不是 Fc");
        }
        
        // FiVSE
        var row25 = configSheet.Row(25);
        if (row25.Cell("A").GetValue<string>() == "FiVSE")
        {
            var fiVSE = row25.Cell("B").GetValue<float>();
            vesselEeConfig.FiVSE = fiVSE;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第二十五行 A 列不是 FiVSE");
        }
        
        // CIIDefaultBound
        var row26 = configSheet.Row(26);
        if (row26.Cell("A").GetValue<string>() == "CIIDefaultBound")
        {
            var ciiDefaultBound = row26.Cell("B").GetValue<string>();
            var ciiDefaultBoundStrs = ciiDefaultBound.Split(',');

            vesselEeConfig.CIIDefaultBound = new CIIDefaultBound()
            {
                Min = float.Parse(ciiDefaultBoundStrs[0].Trim()),
                Max = float.Parse(ciiDefaultBoundStrs[1].Trim())
            };
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第二十六行 A 列不是 CIIDefaultBound");
        }
        
        // LogAbstractPass
        var row27 = configSheet.Row(27);
        if (row27.Cell("A").GetValue<string>() == "LogAbstractPass")
        {
            var logAbstractPass = row27.Cell("B").GetValue<string>();
            vesselEeConfig.LogAbstractPass = logAbstractPass;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第二十七行 A 列不是 LogAbstractPass");
        }
        
        // IsFuelAutoCollect
        var row28 = configSheet.Row(28);
        if (row28.Cell("A").GetValue<string>() == "IsFuelAutoCollect")
        {
            var isFuelAutoCollect = row28.Cell("B").GetValue<bool>();
            vesselEeConfig.IsFuelAutoCollect = isFuelAutoCollect;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第二十八行 A 列不是 IsFuelAutoCollect");
        }
        
        // IsGpsAutoCollect
        var row29 = configSheet.Row(29);
        if (row29.Cell("A").GetValue<string>() == "IsGpsAutoCollect")
        {
            var isGpsAutoCollect = row29.Cell("B").GetValue<bool>();
            vesselEeConfig.IsGpsAutoCollect = isGpsAutoCollect;
        }
        else
        {
            throw new UserFriendlyException("Config Sheet 第二十九行 A 列不是 IsGpsAutoCollect");
        }
        
        // V-CII
        var vCIISheet = workbook.Worksheet("V-CII") ?? throw new UserFriendlyException("Excel 文件中没有 V-CII Sheet");
        var vCIIRows = vCIISheet.RangeUsed()?.RowsUsed().Skip(1) ?? throw new UserFriendlyException("V-CII Sheet 中没有数据");
        foreach (var vCIIRow in vCIIRows)
        {
            var v = vCIIRow.Cell("A").GetValue<decimal>();
            var cii = vCIIRow.Cell("B").GetValue<decimal>();
            vesselEeConfig.VToCIIs.Add(new VToCII(v, cii));
        }
        
        // 插入数据库
        await _vesselEeConfigRepository.InsertAsync(vesselEeConfig);
        _logger.LogInformation("VesselEeConfig 导入成功，Id: {Id}", vesselEeConfig.Id);
    }
}